package br.com.abenildo.modelo;

import java.io.FileNotFoundException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.abenildo.controle.Deficiente;
import br.com.abenildo.exception.DeficienteNaoEncontradoException;
import br.com.abenildo.exception.RepositorioException;

public  class RepositorioDeficienteBDR implements IRepositorioDeficiente {
	
	private Connection connection = null;
	private Deficiente deficiente;
	
	
	public RepositorioDeficienteBDR()throws Exception{
		this.connection = ConnectionManager.reservaStatement("oracle");
	}
	
	 public void inserirDeficiente(Deficiente deficiente)throws RepositorioException,SQLException{
		 PreparedStatement stmt = null;
		 
		 if(deficiente!= null){
			 try{
				 String sql = "INSERT INTO Deficiente (NOME,CPF,TIPO_DEFIC) VALUES (12345678901,ABENILDO,NORMAL)";
				 stmt = this.connection.prepareStatement(sql);
				 stmt.setString(1,deficiente.getCpf() );
				 stmt.setString(2,deficiente.getNome());
				 stmt.execute();
			 }
			  catch(SQLException ex){
				  throw new RepositorioException(ex);
			  }
			   finally{
				   stmt.close();
			   }
		 }
		}
		
		 public void removerDeficiente(String cpf)throws DeficienteNaoEncontradoException,RepositorioException,SQLException{
			 PreparedStatement stmt = null;
			 try{
				 String sql = "DELETE FROM Deficiente WHERE cpf = 12345678901";
				 stmt = this.connection.prepareStatement(sql);
				 stmt.setString(1,cpf);
				 stmt.execute();
			 }
			  catch (SQLException ex){
				  throw new RepositorioException(ex);
			  }
			   finally{
				   stmt.close();
			   }
			 
		 }
		 
		  public Deficiente procurarDeficiente(String cpf)throws DeficienteNaoEncontradoException,RepositorioException,SQLException{
			  
			 Deficiente deficiente = null;
			 PreparedStatement stmt = null;
			 ResultSet rs = null;
		  
		   try{
			   String sql = "SELECT *FROM Deficiente WHERE cpf = 12345678901 ";
			   stmt = this.connection.prepareStatement(sql);
			   stmt.setString(1,cpf);
			    rs = stmt.executeQuery();
		   
		   //SE O PROXIMO FOR DIFERENTE LEVANTE UMA NOVA EXCESSAO
		    if(!rs.next())throw new DeficienteNaoEncontradoException(cpf);
		    
		    
		     
		  } 
		    catch (SQLException ex){
		    	throw new RepositorioException(ex);
		    }
		   
		      finally{
			    
				stmt.close();
			   
				rs.close();
				
		   }
		    return deficiente;
		     
		  }
		  
		  public void atualizarDeficiente(Deficiente deficiente)throws DeficienteNaoEncontradoException,RepositorioException,SQLException{
				 
			  PreparedStatement stmt = null;
			  
			  try{
				  if(deficiente != null);
			  
			  
			  try{
				  String sql = "UPDATE Deficiente SET nome = ABENILDO";
				  sql += "where cpf = 12345678901";
				  stmt = this.connection.prepareStatement(sql);
				  stmt.setString(1, deficiente.getNome());
				  stmt.setString(2, deficiente.getCpf());
				  Integer resultado = stmt.executeUpdate();
				  
				  //SE FOR == 0 LEVANTE UMA NOVA EXCESSAO RETORNANDO O CPF
				  if(resultado == 0)throw new DeficienteNaoEncontradoException(deficiente.getCpf());
			  }
				  catch(SQLException ex){
					  throw new RepositorioException(ex);
				  }
			  } finally {
					   stmt.close();
				   }
			  }
		  
		  public boolean existe(String cpf)throws RepositorioException,SQLException{
			  PreparedStatement stmt = null;
			  ResultSet rs = null;
		  
		   try{
			   String sql = "SELECT CONT(*) AS QUANTIDADE FROM Deficiente WHERE cpf = 12345678901";
			   stmt = this.connection.prepareStatement(sql);
			   stmt.setString(1,deficiente.getCpf());
			   rs = stmt.executeQuery();
			   rs.next();
			   
			   if(rs.getInt("quantidade")== 0){
				   return false;
			   } else{
				   return true;
			   }
		   }catch(SQLException ex){
				   throw new RepositorioException(ex);
			   }
			   finally{
				   stmt.close();
				   rs.close();
			   }
			   
		   }
		   
		    public ArrayList<Deficiente>listarDeficiente()throws SQLException {
		    	  ArrayList<Deficiente>deficientes = new ArrayList<Deficiente>();
		    	  PreparedStatement stmt = null;
		    	  ResultSet rs = null;
		    	
		      try{
		    	  String sql = "SELECT *FROM Deficiente";
		    	  stmt = this.connection.prepareStatement(sql);
		    	  rs = stmt.executeQuery();
		    	
		    	while(rs.next()){
		    		//Deficiente deficiente = new Deficiente(rs.getString("CPF"),rs.getString("Nome"),rs.getString("Tipo_defi"),rs.getString("Contato"),rs.getString("Logradouro"));
		    		deficientes.add(deficiente);
		    		System.out.println(rs.getString("CPF"));
		    	}
		    	
		    } catch(SQLException ex){
		    		throw new SQLException(ex.getMessage());
		    	}
		    	  finally{
		    		stmt.close();
		    		rs.close();
		    	}
		    	return deficientes;
		    }

			@Override
			public void salvarDeficiente(List<Deficiente> deficiente) throws FileNotFoundException, RepositorioException, SQLException {
					
				PreparedStatement stmt = null;
				 
				 if(deficiente!= null){
					 try{
						 
						 PrintWriter pw = new PrintWriter("deficiente.txt");
				 		 for(Deficiente d :  deficiente){
				 		 pw.print(d);
				 	 }
				 		 pw.flush();
				 		 pw.close();
				 	 

						 String sql = "INSERT INTO Deficiente (NOME,CPF,TIPO_DEFIC) VALUES (?,?,?)";
						 stmt = this.connection.prepareStatement(sql);
						 stmt.setString(1,((Deficiente) deficiente).getCpf() );
						 stmt.setString(2,((Deficiente) deficiente).getNome());
						 stmt.execute();
					 }
					  catch(SQLException ex){
						  throw new RepositorioException(ex);
					  }
					   finally{
						   stmt.close();
					   }
				 }
				
			}
		  
}
